<?php
class ArticleModel
{
    public static function GetArticle($ID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        $query = "select Articles.*, Categories.Name as CategoryName ".
            "from Articles, Categories where Articles.ID = '$ID' and Articles.CategoryID = Categories.ID";

        try {
                $result = $db->fetchRow($query);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
        return $result;
    }
    public static function GetAllArticle()
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        $query = "select Articles.*, Categories.Name as CategoryName ".
            "from Articles, Categories where Articles.CategoryID = Categories.ID";

        try {
                $result = $db->fetchAll($query);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
        return $result;
    }
    public static function GetArticleDt($articleID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        $query = "select * from ArticleDt where ArticleID = '$articleID'";
        
        try {
                $result = $db->fetchAll($query);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
        return $result;
    }
    
    public static function GetArticleDtOrderByOrder($articleID, $isInc)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        $query = "select * from ArticleDt where ArticleID = '$articleID' order by Orders";
        if($isInc==false)
            $query .= " desc";
        
        try {
                $result = $db->fetchAll($query);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
        return $result;
    }
    
    public static function GetArticleDtFromID($articleDtID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        $query = "select * from ArticleDt where ID = '$articleDtID'";
        
        try {
                $result = $db->fetchRow($query);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
        return $result;
    }
    
    public static function UpdateArticleDtContent($articleDtID, $content)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        try {
            $where = "ID = '$articleDtID'";
            $set = array('Content' => $content);
            $db->update('ArticleDt', $set, $where);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
    }

        
    public static function ChangeOrderArticleDt($articleDtID, $isIncrease)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        $articleModel = new ArticleModel();
        
        //ArticleDt đang chỉnh sửa
        $articleDtActive = $articleModel->GetArticleDtFromID($articleDtID);
        $articleDtActiveID=$articleDtActive['ID'];
        $articleDtActiveOrder = $articleDtActive['Orders'];
        
        //Lấy danh sách các ArticleDt có chung ArticleID với Article đang chỉnh sửa
        if($isIncrease==true)
            $articleDts = $articleModel->GetArticleDtOrderByOrder($articleDtActive['ArticleID'], true);
        else $articleDts = $articleModel->GetArticleDtOrderByOrder($articleDtActive['ArticleID'], false);
        
        
        //Duyệt danh sách để lấy ra phần tử kế tiếp ngay sau ArticleDt đang chỉnh sửa
        foreach($articleDts as $key=>$articleDt)
        {
            if($articleDt['ID']==$articleDtActiveID)
            {
                $articleDtActiveNext = $articleDts[$key+1];
                $articleDtActiveNextID = $articleDtActiveNext['ID'];
                $articleDtActiveNextOrder = $articleDtActiveNext['Orders'];
                break;
            }
        }
        
        try {
            $where = "ID = '$articleDtActiveID'";
            $set = array('Orders' => $articleDtActiveNextOrder);
            $db->update('ArticleDt', $set, $where);
            
            $where = "ID = '$articleDtActiveNextID'";
            $set = array('Orders' => $articleDtActiveOrder);
            $db->update('ArticleDt', $set, $where);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    public static function AddArticleDtText($content, $articleID,$order)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        $data = array('ID'=>'', 'Content'=>$content, 'Orders'=>$order, 'ArticleID'=>$articleID);
        
        try {
            $db->insert('ArticleDt', $data);
        }
        catch(Zend_Db_Exception $e)
        {
            echo 'DB ERROR : '.$e->getMessage();
        }
    }
    //Thêm một ArticleDt mới kiểu text, có order ngay sau một ArticleDt khác
    public static function AddArticleDtTextAfter($articleDtID, $content)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        $articleModel = new ArticleModel();
        
        //Article mốc
        $articleDtMark = $articleModel->GetArticleDtFromID($articleDtID);
        $articleID=$articleDtMark['ArticleID'];
        
        //Dịch chuyển các article khác xuống
        $articleDts = $articleModel->GetArticleDtOrderByOrder($articleID, true);
        foreach ($articleDts as $articleDt)
        {
            if($articleDt['Orders']<$articleDtMark['Order']
                    ||$articleDt['ID']==$articleDtMark['ID'])
                    continue;
            $newOrder = $articleDt['Orders']+1;
            try {
                $where = "ID = '{$articleDt['ID']}'";
                $set = array('Orders' => $newOrder);
                $db->update('ArticleDt', $set, $where);
            }
            catch(Zend_Db_Exception $e){
                    echo 'DB ERROR : '.$e->getMessage();
            }
        }
        
        //Thêm articleDt
        $articleModel->AddArticleDtText($content, $articleID, $articleDtMark['Orders']+1);
    }
    
    //Thêm một ArticleDt có Order nhỏ nhất (trên cùng).
    public static function AddArticleDtTextTop($articleID, $content)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        //Dịch chuyển các article khác xuống
        $articleDts = ArticleModel::GetArticleDtOrderByOrder($articleID, true);
        $articleDtOrderTop = $articleDts[0]['Orders'];
        foreach ($articleDts as $articleDt)
        {
            $newOrder = $articleDt['Orders']+1;
            try {
                $where = "ID = '{$articleDt['ID']}'";
                $set = array('Orders' => $newOrder);
                $db->update('ArticleDt', $set, $where);
            }
            catch(Zend_Db_Exception $e){
                    echo 'DB ERROR : '.$e->getMessage();
            }
        }
        if(isset ($articleDtOrderTop)==false) $articleDtOrderTop=0;
        //Thêm articleDt
        ArticleModel::AddArticleDtText($content, $articleID, $articleDtOrderTop);
    }
    
    public static function DeleteArticleDt($articleDtID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        try {
            $db->delete('ArticleDt', "ID = '$articleDtID'");
        }
        catch(Zend_Db_Exception $e){
            echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    //Cập nhật thông tin ArticleDt loại hình ảnh
    public static function UpdateArticleDtImage($articleDtID, $imageURL, $imageDes)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        try {
            $where = "ID = '$articleDtID'";
            $set = array('Content' => $imageDes,
                            'ImageURL' =>$imageURL);
            $db->update('ArticleDt', $set, $where);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    public static function AddArticleDtImage($imageURL, $imageDes, $articleID,$order)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        $data = array('ID'=>'', 'Content'=>$imageDes, 'ImageURL'=>$imageURL,
            'Orders'=>$order, 'ArticleID'=>$articleID);
        
        try {
            $db->insert('ArticleDt', $data);
        }
        catch(Zend_Db_Exception $e)
        {
            echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    //Thêm một ArticleDt mới kiểu hình ảnh, có order ngay sau một ArticleDt khác
    public static function AddArticleDtImageAfter($articleDtID, $imageURL, $imageDes)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        $articleModel = new ArticleModel();
        
        //Article mốc
        $articleDtMark = $articleModel->GetArticleDtFromID($articleDtID);
        $articleID=$articleDtMark['ArticleID'];
        
        //Dịch chuyển các article khác xuống
        $articleDts = $articleModel->GetArticleDtOrderByOrder($articleID, true);
        foreach ($articleDts as $articleDt)
        {
            if($articleDt['Orders']<$articleDtMark['Order']
                    ||$articleDt['ID']==$articleDtMark['ID'])
                    continue;
            $newOrder = $articleDt['Orders']+1;
            try {
                $where = "ID = '{$articleDt['ID']}'";
                $set = array('Orders' => $newOrder);
                $db->update('ArticleDt', $set, $where);
            }
            catch(Zend_Db_Exception $e){
                    echo 'DB ERROR : '.$e->getMessage();
            }
        }
        
        //Thêm articleDt
        $articleModel->AddArticleDtImage($imageURL, $imageDes, $articleID, $articleDtMark['Orders']+1);
    }
    
    //Thêm một ArticleDt loại hình ảnh có Order nhỏ nhất (trên cùng).
    public static function AddArticleDtImageTop($articleID, $imageURL, $imageDes)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        //Dịch chuyển các article khác xuống
        $articleDts = ArticleModel::GetArticleDtOrderByOrder($articleID, true);
        $articleDtOrderTop = $articleDts[0]['Orders'];
        
        foreach ($articleDts as $articleDt)
        {
            $newOrder = $articleDt['Orders']+1;
            try {
                $where = "ID = '{$articleDt['ID']}'";
                $set = array('Orders' => $newOrder);
                $db->update('ArticleDt', $set, $where);
            }
            catch(Zend_Db_Exception $e){
                    echo 'DB ERROR : '.$e->getMessage();
            }
        }
        ;
        //Thêm articleDt
        if(isset ($articleDtOrderTop)==false) $articleDtOrderTop=0;
        ArticleModel::AddArticleDtImage($imageURL, $imageDes, $articleID, $articleDtOrderTop);
    }
    
    public static function UpdateTitle($articleID, $title)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        try {
            $where = "ID = '$articleID'";
            $set = array('Title' => $title);
            $db->update('Articles', $set, $where);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    public static function UpdateArticleCategoryID($articleID, $categoryID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        try {
            $where = "ID = '$articleID'";
            $set = array('CategoryID' => $categoryID);
            $db->update('Articles', $set, $where);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    public static function Delete($IDs)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        try {
            foreach ($IDs as $ID){
                $db->delete('articles', "ID = '$ID'");
            }
        }
        catch(Zend_Db_Exception $e){
            echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    public static function AddArticle($title, $createdByID, $categoryID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");
        
        $now = date("Y-m-d H:i:s"); 
        $data = array('ID'=>'', 'Title'=>$title, 'CreatedByID'=>$createdByID,
            'CategoryID'=>$categoryID, 'CreatedTime'=>$now);
        $query = 'select max(ID) from Articles';
        try {
            $db->insert('Articles', $data);
            return $db->fetchOne($query);
        }
        catch(Zend_Db_Exception $e)
        {
            echo 'DB ERROR : '.$e->getMessage();
        }
    }
    
    public static function GetArticleByCategoryID($categoryID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        if($categoryID != -1){
            $query = "select Articles.*, Categories.Name as CategoryName from Articles, Categories".
                    " where Articles.CategoryID = Categories.ID and CategoryID = '$categoryID'";
        }
        else {
            $query = "select Articles.*,  Categories.Name as CategoryName".
                    " from Articles, Categories".
                    " where Articles.CategoryID = Categories.ID";
        }
        try {
            $result = $db->fetchAll($query);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
        return $result;
    }
    
    //Lấy đoạn text đầu tiên
    public static function GetArticleDtIntro($articleID)
    {
        include_once 'sql_connector.php';
        $db = SQLConnector::connect();
        $db->query("SET NAMES 'utf8'");

        $query = "select ArticleDt.Content ".
            "from ArticleDt ".
            "where ArticleID = $articleID and ImageURL='' ".
            "and Orders = (select min(Orders) from ArticleDt where ArticleID = $articleID and ImageURL='')";
        try {
            $result = $db->fetchOne($query);
        }
        catch(Zend_Db_Exception $e){
                echo 'DB ERROR : '.$e->getMessage();
        }
        return $result;
    }
}

